package com.fr.data;

import com.fr.base.Parameter;
import com.fr.general.data.TableDataException;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

public class StoreReportByYear extends AbstractTableData {
    // 字段名枚举
    enum FIELD_NAME {
        STORE_ID, STORE_NAME, SALES_AMOUNT, REPORT_DATE
    }

    // 列名数组，保存程序数据集所有列名
    private String[] columNames;
    // 保存查询得到列值
    private LinkedHashSet<LinkedHashMap<String, Object>> rowData;

    // 构造函数，定义表结构，该表有10个数据列，列名为column#0，column#1，。。。。。。column#9
    public StoreReportByYear() {
        // 定义tableName参数
//        setDefaultParameters(new Parameter[]{new Parameter("tableName")});
        // 定义程序数据集列名
        this.parameters = new Parameter[]{new Parameter("beginDate"), new Parameter("endDate")};
        // 填充字段名
        columNames = new String[FIELD_NAME.values().length];
        int i = 0;
        for (FIELD_NAME fieldName : FIELD_NAME.values()) {
            columNames[i] = fieldName.toString();
            i++;
        }
    }

    @Override
    public int getColumnCount() throws TableDataException {
        return columNames.length;
    }

    @Override
    public String getColumnName(int columnIndex) throws TableDataException {
        return columNames[columnIndex];
    }

    @Override
    public int getRowCount() throws TableDataException {
        queryData();
        return rowData.size();
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        queryData();
        int tempRowIndex = 0;
        for (LinkedHashMap<String, Object> row : rowData) {
            if (tempRowIndex == rowIndex) {
                return row.get(columNames[columnIndex]);
            }
            tempRowIndex += 1;
        }
        return null;
    }

    // 准备数据
    public void queryData() {
        // 确保只被执行一次
        if (rowData != null) {
            return;
        }
        // 保存得到的数据库表名
//        String beginDate = parameters[0].getValue().toString();
//        String city = parameters[1].getValue().toString();

        String beginDate = "FRI Jun 01 2018 00:00:00 GMT+0800";
        String endDate = "Sun Jul 01 2018 00:00:00 GMT+0800";

        Date date1 = new Date(beginDate);
        beginDate = new SimpleDateFormat("yyyyMMdd").format(date1);
        Date date2 = new Date(endDate);
        endDate = new SimpleDateFormat("yyyyMMdd").format(date2);
        long start = System.currentTimeMillis();
        // 保存得到的结果集
        rowData = new LinkedHashSet<LinkedHashMap<String, Object>>();
        // 下面开始建立数据库连接，按照刚才的SQL语句进行查询
        Connection conn = ConnectUtil.getConnection();
        try {
            Statement stmt = conn.createStatement();
            //查询经销商Id
            String partyIdSql = "SELECT s.party_id FROM zdev_store_info s  GROUP BY s.party_id ";
            List partyList = new ArrayList();
            ResultSet partyIdReportRs = stmt.executeQuery(partyIdSql.toString());
            LinkedHashMap<String, Object> partyMap;
            while (partyIdReportRs.next()) {
                partyMap = new LinkedHashMap<String, Object>();
                partyMap.put("partyId", partyIdReportRs.getString(1));
                partyList.add(partyMap);
            }
            partyIdReportRs.close();

            List reportList = new ArrayList();
            for (int i = 0; i < partyList.size(); i++) {
                LinkedHashMap<String, Object> map = (LinkedHashMap<String, Object>) partyList.get(i);
                String partyId = String.valueOf(map.get("partyId"));
                //判断表是否存在
                String tableExist = "select count(1) from user_tables where table_name = 'ZDEV_SALES_DTL" + partyId + "_V2'";
                ResultSet tableExistResultSet = stmt.executeQuery(tableExist);
                String tableExistFlag = "";
                while (tableExistResultSet.next()) {
                    tableExistFlag = tableExistResultSet.getString(1);
                }
                if ("1".equals(tableExistFlag)) {
                    StringBuffer storeReport = new StringBuffer();
                    storeReport.append("SELECT T.store_id,ps.store_name,SUM(t.sales_amount) AS sale_amount, t.extend2");
                    storeReport.append(" FROM ZDEV_PS_DTL" + partyId + "_V2 T");
                    storeReport.append(" LEFT JOIN PRODUCT_STORE PS ON PS.PRODUCT_STORE_ID=T.store_id");
                    storeReport.append(" LEFT JOIN PRODUCT_STORE_TYPE ST ON PS.PRODUCT_STORE_TYPE_ID = ST.PRODUCT_STORE_TYPE_ID");
                    storeReport.append(" LEFT JOIN GEO G ON PS.GEO_ID = G.GEO_ID");
                    storeReport.append(" LEFT JOIN PARTY_GROUP PG ON PS.PAY_TO_PARTY_ID = PG.PARTY_ID");
                    storeReport.append(" WHERE dim_type = '01'");
                    storeReport.append(" GROUP BY T.store_id,ps.store_name,t.extend2");
                    storeReport.append(" ORDER BY  T.store_id, t.extend2");
                    ResultSet productReportRs = stmt.executeQuery(storeReport.toString());
                    LinkedHashMap<String, Object> productReportMap;
                    while (productReportRs.next()) {
                        StoreQueryInfo info = new StoreQueryInfo();
                        info.setStoreId(productReportRs.getString("store_id"));
                        info.setStoreName(productReportRs.getString("store_name"));
                        info.setSalesAmount(productReportRs.getBigDecimal("sale_amount"));
                        info.setReportDate(productReportRs.getString("extend2"));
                        reportList.add(info);

                    }
                    productReportRs.close();
                }
            }
            //根据report_date排序
            Collections.sort(reportList, new Comparator() {
                @Override
                public int compare(Object o1, Object o2) {
                    if (o1 instanceof StoreQueryInfo && o2 instanceof StoreQueryInfo) {
                        StoreQueryInfo e1 = (StoreQueryInfo) o1;
                        StoreQueryInfo e2 = (StoreQueryInfo) o2;
                        return e1.getReportDate().compareTo(e2.getReportDate());
                    }
                    throw new ClassCastException("不能转换为StoreQueryInfo类型");
                }
            });
            //根据store_id排序
            Collections.sort(reportList, new Comparator() {
                @Override
                public int compare(Object o1, Object o2) {
                    if (o1 instanceof StoreQueryInfo && o2 instanceof StoreQueryInfo) {
                        StoreQueryInfo e1 = (StoreQueryInfo) o1;
                        StoreQueryInfo e2 = (StoreQueryInfo) o2;
                        return e1.getStoreId().compareTo(e2.getStoreId());
                    }
                    throw new ClassCastException("不能转换为StoreQueryInfo类型");
                }
            });

            //按照枚举类放置数据
            for (int i = 0; i < reportList.size(); i++) {
                StoreQueryInfo info = (StoreQueryInfo) reportList.get(i);
                LinkedHashMap<String, Object> row = new LinkedHashMap<String, Object>();
                row.put(FIELD_NAME.STORE_ID.toString(), info.getStoreId());
                row.put(FIELD_NAME.STORE_NAME.toString(), info.getStoreName());
                row.put(FIELD_NAME.SALES_AMOUNT.toString(), info.getSalesAmount());
                row.put(FIELD_NAME.REPORT_DATE.toString(), info.getReportDate());
                rowData.add(row);
            }
            long end = System.currentTimeMillis();
            System.out.println("耗时：" + String.valueOf((end - start) / 1000) + "s");
            System.out.println("===================================s");
//            System.out.println(rowData.toString());
            // 释放数据库资源
            stmt.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // 释放一些资源，因为可能会有重复调用，所以需释放valueList，将上次查询的结果释放掉
    @Override
    public void release() throws Exception {
        super.release();
        this.rowData = null;
    }
}
